package org.i1510.base.utils.sqliteJdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.i1510.base.utils.KBProperties;
import org.i1510.base.utils.StringUtils;

/**
 * @author Administrator sqlite的数据操作工具类
 */
public class DbUtils {
	private String dbAddress = "";
	private Connection conn = null;

	private Connection getConn(String dbAddress) {
		try {
			Class.forName("org.sqlite.JDBC");
			if(StringUtils.isStringEmpty(dbAddress)){
				this.dbAddress = KBProperties.SQLITE_ADDRESS;
			}
			conn = DriverManager.getConnection("jdbc:sqlite:" + this.dbAddress);

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;

	}

	/**
	 * @Author Rambo
	 * @Desc：执行executeUpdate
	 * @param sql
	 * @return TODO
	 * @Version DbUtils.java,v 1.1 2014-9-30 下午5:12:10
	 */
	public boolean executeUpdate(String sql) {
		if (null == this.conn) {
			this.conn = this.getConn(dbAddress);
		}

		Statement stmt = null;
		int count = 0;
		try {

			stmt = conn.createStatement();
			count = stmt.executeUpdate(sql);
		} catch (Exception ex) {
			ex.printStackTrace();
			count = 0;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
		}
		if (count >= 0) {
			return true;
		} else {
			return false;
		}

	}
	/**
	*@Author Rambo
	*@Desc：执行executeQuery
	*@param sql
	*@return TODO
	*@Version  DbUtils.java,v 1.1 2014-9-30 下午5:16:10 
	*/
	public  List executeQuery(String sql) {
		List list = new ArrayList();
		if (null == this.conn) {
			this.conn = this.getConn(dbAddress);
		}
		Statement stm = null;
		ResultSet rs = null;
		ResultSetMetaData rms = null;
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);
			rms = rs.getMetaData();
			while (rs.next()) {
				Map map = new HashMap();
				for (int i = 1; i <= rms.getColumnCount(); i++) {
					String field = rms.getColumnName(i).toLowerCase();
					map.put(field, rs.getString(field));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stm.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
		return list;
	}
	/**
	*@author Kenshinbo@163.com
	*@描述：批量处理
	*@param sqls
	*@return 
	*@version  DbUtils.java,v 1.1 Oct 6, 2014 5:18:58 PM KB
	*/
	public  boolean tranExecSQL(String[] sqls) {
		Statement stmt = null;
		boolean t = false;
		try {
			if (null == this.conn) {
				this.conn = this.getConn(dbAddress);
			}
			this.conn.setAutoCommit(false);
			stmt = this.conn.createStatement();
			for (int i = 0; i < sqls.length; i++) {
				if (sqls[i] != null) {
					stmt.addBatch(sqls[i]);
				}
			}
			int[] rtn = stmt.executeBatch();
			this.conn.commit();
			this.conn.setAutoCommit(true);
			t = true;
		} catch (Exception ex) {
			ex.printStackTrace();
			try {
				this.conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
			if (this.conn != null) {
				try {
					this.conn.close();
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
		}
		return t;
	}
	public  int executeUpdate(String sql,Object []o) {
		PreparedStatement stmt = null;
		int flag = 0;
		try {
			if (null == this.conn) {
				this.conn = this.getConn(dbAddress);
			}
			stmt = conn.prepareStatement(sql);
			if (o!=null){
				for(int i=0;i<o.length;i++){
					stmt.setObject(i+1, o[i]);
				}
			}
			flag = stmt.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
			flag = 0;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception ex) {
					ex.printStackTrace();
				}
			}
		}
		return flag;
	}
	/**
	 * @Author Rambo
	 * @Desc：
	 * @param args
	 *            TODO
	 * @Version DbUtils.java,v 1.1 2014-9-30 下午4:24:17
	 */
	public static void main1(String[] args) {
//		DbUtils db = new DbUtils();
//		db.executeUpdate(" drop table IF EXISTS kb_html_all;");
		
		DbUtils db1 = new DbUtils();
		db1.executeUpdate(" create table IF NOT EXISTS kb_html_all(id varchar(36),name varchar(500));");
		
		DbUtils db2 = new DbUtils();
		db2.executeUpdate( "insert into kb_html_all values('1','我的世界');" ); //插入数据
		
		DbUtils db3 = new DbUtils();
		db3.executeUpdate( "insert into kb_html_all values('2','我的22世界');" ); //插入数据
		
		DbUtils db4 = new DbUtils();
		db4.executeUpdate( "insert into kb_html_all values('3','我的33世界');" ); //插入数据
		
		DbUtils db0 = new DbUtils();
		List list = db0.executeQuery("select name from kb_html_all;"); //查询数据 
		for (Iterator iterator = list.iterator(); iterator.hasNext();) {
			Object object = (Object) iterator.next();
			System.out.println(object);
		}

	}
	public static void main2(String[] args) {

		//创建爬虫数据库（问题列表）
		DbUtils db0 = new DbUtils();
		db0.executeUpdate(" drop table IF  EXISTS km_list;");
		
		DbUtils db1 = new DbUtils();
		db1.executeUpdate(" create table IF NOT EXISTS km_list(title varchar(500),href varchar(250),tag varchar(100));");
		
		DbUtils db2 = new DbUtils();
		db2.executeUpdate( "insert into km_list values('1','我的世界','ss');" ); //插入数据
		
	
		DbUtils db00 = new DbUtils();
		List list = db00.executeQuery("select * from km_list;"); //查询数据 
		for (Iterator iterator = list.iterator(); iterator.hasNext();) {
			Object object = (Object) iterator.next();
			System.out.println(object);
		}

	}
	public static void main3(String[] args) {


		//创建爬虫数据库（问题答案）
		DbUtils db0 = new DbUtils();
		db0.executeUpdate(" drop table IF  EXISTS km_an;");
		
		DbUtils db1 = new DbUtils();
		db1.executeUpdate(" create table IF NOT EXISTS km_an(title varchar(500),href varchar(250),tag varchar(100),descs varchar(1000),answer varchar(4000));");
		
		DbUtils db2 = new DbUtils();
		db2.executeUpdate( "insert into km_an values('1','我的世界','ss','问题描述','答案');" ); //插入数据
		
		DbUtils db3 = new DbUtils();
		String[] strs = new String[1];
		strs[0] = "占位符插入";
		db3.executeUpdate( "insert into km_an values('1','我的世界','ss','问题描述',?);",strs ); //插入数据
	
		DbUtils db00 = new DbUtils();
		List list = db00.executeQuery("select * from km_an;"); //查询数据 
		for (Iterator iterator = list.iterator(); iterator.hasNext();) {
			Object object = (Object) iterator.next();
			System.out.println(object);
		}

	

	}
	public static void main(String[] args) {
		//创建爬虫数据库
//		DbUtils db0 = new DbUtils();
//		db0.executeUpdate(" drop table IF  EXISTS km_an;");
//		
//		DbUtils db00 = new DbUtils();
//		db00.executeUpdate(" drop table IF  EXISTS km_list;");
//		
//		
//		DbUtils db1 = new DbUtils();
//		db1.executeUpdate(" create table IF NOT EXISTS km_an(title varchar(500),href varchar(250),tag varchar(100),descs varchar(1000),answer varchar(4000));");
//		
//		DbUtils db2 = new DbUtils();
//		db2.executeUpdate(" create table IF NOT EXISTS km_list(title varchar(500),href varchar(250),tag varchar(100));");

		

		
//		DbUtils db1_ = new DbUtils();
//		db1_.executeUpdate(" drop table IF  EXISTS job_caibaojian;");
//		
//		DbUtils db1_1 = new DbUtils();
//		db1_1.executeUpdate(" create table IF NOT EXISTS job_caibaojian(title varchar(500),href varchar(250),tag varchar(100),isFlag varchar(1));");
		
		DbUtils db2_ = new DbUtils();
		db2_.executeUpdate(" drop table IF  EXISTS job_caibaojian_dt;");
		
		DbUtils db2_2 = new DbUtils();
		db2_2.executeUpdate(" create table IF NOT EXISTS job_caibaojian_dt(title varchar(500),href varchar(250),tag varchar(100),isFlag varchar(1),descs varchar(1000),answer varchar(4000));");
	}
}
